const mysql = require('mysql2/promise');
const fs = require('fs').promises;
const path = require('path');

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || '43.143.253.188',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'nextjs',
  password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
  database: process.env.DB_NAME || 'nextjs',
  timezone: '+08:00'
};

// 迁移文件路径 - 使用简化版本
const MIGRATION_FILE = path.join(__dirname, '../database/alter-map-areas-table-simple.sql');

async function runMigration () {
  let connection;

  try {
    console.log('🚀 开始执行地图区域表结构修改...');
    console.log('📊 数据库配置:', {
      host: dbConfig.host,
      port: dbConfig.port,
      user: dbConfig.user,
      database: dbConfig.database,
      timezone: dbConfig.timezone
    });

    // 连接数据库
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');

    // 检查迁移文件是否存在
    try {
      await fs.access(MIGRATION_FILE);
      console.log('✅ 找到迁移文件:', MIGRATION_FILE);
    } catch (error) {
      throw new Error(`迁移文件不存在: ${MIGRATION_FILE}`);
    }

    // 读取迁移文件内容
    const migrationSQL = await fs.readFile(MIGRATION_FILE, 'utf8');
    console.log('📋 迁移文件内容预览:');
    console.log('----------------------------------------');
    console.log(migrationSQL.split('\n').slice(0, 20).join('\n'));
    console.log('...');
    console.log('----------------------------------------');

    // 备份当前表结构
    console.log('\n💾 备份当前表结构...');
    const backupFile = path.join(__dirname, `../database/map_areas_backup_${new Date().toISOString().replace(/[:.]/g, '-')}.sql`);

    try {
      // 检查表是否存在
      const [tables] = await connection.execute(`
                SELECT COUNT(*) as count 
                FROM information_schema.tables 
                WHERE table_schema = ? AND table_name = 'map_areas'
            `, [dbConfig.database]);

      if (tables[0].count > 0) {
        // 获取表结构
        const [createTable] = await connection.execute('SHOW CREATE TABLE map_areas');
        const backupSQL = `-- 地图区域表结构备份\n-- 备份时间: ${new Date().toISOString()}\n\n${createTable[0]['Create Table']};\n`;

        await fs.writeFile(backupFile, backupSQL);
        console.log('✅ 表结构已备份到:', backupFile);
      } else {
        console.log('⚠️  表 map_areas 不存在，跳过备份');
      }
    } catch (error) {
      console.log('⚠️  备份表结构失败:', error.message);
    }

    // 执行迁移
    console.log('\n🔧 执行表结构修改...');

    // 分割SQL语句并逐条执行
    const sqlStatements = migrationSQL
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0 && !stmt.startsWith('--'));

    // 先执行ALTER TABLE语句
    console.log('📝 第一步：添加新字段...');
    for (let i = 0; i < sqlStatements.length; i++) {
      const sql = sqlStatements[i];
      if (sql.trim() && sql.toUpperCase().startsWith('ALTER TABLE')) {
        try {
          console.log(`执行ALTER TABLE: ${sql.substring(0, 50)}...`);
          await connection.execute(sql);
        } catch (error) {
          if (error.code === 'ER_DUP_FIELDNAME') {
            console.log(`⚠️  字段已存在，跳过: ${error.message}`);
          } else {
            throw error;
          }
        }
      }
    }

    // 再执行UPDATE语句
    console.log('📝 第二步：更新数据...');
    for (let i = 0; i < sqlStatements.length; i++) {
      const sql = sqlStatements[i];
      if (sql.trim() && sql.toUpperCase().startsWith('UPDATE')) {
        try {
          console.log(`执行UPDATE: ${sql.substring(0, 50)}...`);
          await connection.execute(sql);
        } catch (error) {
          console.log(`⚠️  UPDATE执行失败，跳过: ${error.message}`);
        }
      }
    }

    console.log('✅ 表结构修改完成');

    // 验证修改结果
    console.log('\n🔍 验证修改结果...');

    try {
      const [result] = await connection.execute(`
                SELECT 
                    COUNT(*) as total_areas,
                    COUNT(parent_id) as areas_with_parent,
                    COUNT(CASE WHEN parent_id IS NULL THEN 1 END) as top_level_areas,
                    COUNT(extension) as areas_with_extension
                FROM map_areas
            `);

      console.log('📊 区域统计:');
      console.log(`  总区域数: ${result[0].total_areas}`);
      console.log(`  有父级区域数: ${result[0].areas_with_parent}`);
      console.log(`  顶级区域数: ${result[0].top_level_areas}`);
      console.log(`  有等级信息区域数: ${result[0].areas_with_extension}`);

      // 显示区域层级关系示例
      const [hierarchyResult] = await connection.execute(`
                SELECT 
                    \`id\`, 
                    \`name\`, 
                    \`parent_id\`, 
                    \`extension\`,
                    CASE 
                        WHEN \`parent_id\` IS NULL THEN '顶级区域'
                        ELSE CONCAT('子区域 (父级ID: ', \`parent_id\`, ')')
                    END as area_type
                FROM \`map_areas\` 
                ORDER BY \`parent_id\` IS NULL DESC, \`id\`
                LIMIT 20
            `);

      console.log('\n📊 区域层级关系示例:');
      hierarchyResult.forEach(row => {
        const name = row.extension ? `${row.name}${row.extension}` : row.name;
        console.log(`  ${row.id.toString().padStart(4)} | ${name.padEnd(20)} | ${row.area_type}`);
      });

    } catch (error) {
      console.log('⚠️  验证失败:', error.message);
    }

    console.log('\n🎉 地图区域表结构修改完成！');
    console.log('\n📝 后续步骤:');
    console.log('1. 重启NestJS应用服务');
    console.log('2. 测试WebSocket地图查询功能');
    console.log('3. 运行测试脚本: node test-map-areas.js');

  } catch (error) {
    console.error('❌ 迁移过程中发生错误:', error.message);
    if (error.code) {
      console.error('错误代码:', error.code);
    }
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 主函数
if (require.main === module) {
  runMigration().catch(console.error);
}

module.exports = { runMigration }; 